﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for PolicyRules
/// </summary>
public class PolicyRules
{
	public PolicyRules(){}

    public void payPremium(int accountID, string policyNo)
    {
        Policy policy = Policy.getPolicy(policyNo);

        Payment payment = new Payment();

        decimal totalPremiumPaid = policy.totalAnnualPremium;
        if ("M".Equals(policy.premiumFrequency))
        {
            totalPremiumPaid = policy.totalAnnualPremium / 12;
        }
        else if ("S".Equals(policy.premiumFrequency))
        {
            totalPremiumPaid = policy.totalAnnualPremium / 2;
        }

        // withdrawal money from saving/current account.
        int transactionId = payment.insertTransaction(accountID, totalPremiumPaid, false, DateTime.Now, "RETIREMENT");

        //Get waiver rider annual premium payable
        decimal waiverRiderPremium = policy.waiverRiderPremium;
        
        //Calculate waiver rider premium payable based on payment frequency (monthly, semi-annually, annually)
        if ("M".Equals(policy.premiumFrequency))
        {
            waiverRiderPremium = policy.waiverRiderPremium / 12;
        }
        else if ("S".Equals(policy.premiumFrequency))
        {
            waiverRiderPremium = policy.waiverRiderPremium / 2;
        }

        decimal amount = totalPremiumPaid - waiverRiderPremium;

        //Get fund-to-premium allocation percentage according to policy years 
        //E.g. Policy year 1 - 15% of premium is investable, remaining 85% of premium for insurance charge, rider insurance charge and other fees
        decimal fundToPremiumPercentage = getFundToPremiumPercentage(policyNo);

        // Refer to the ANNUAL_INSURANCE_CHARGE table for the rate (based on current age, gender and smoker/non-smoker)
        decimal annualBasicInsuranceChargeRate = getAnnualBasicInsuranceChargeRate(policy.nric);
        decimal basicSumInsured = policy.basicSumInsured;
        decimal accountValue = getAccountValue(policyNo);

        // Refer to the ANNUAL_PLUS_RIDER_CHARGE table for the rate (based on current age, gender and smoker/non-smoker)
        decimal annualPlusRiderChargeRate = getAnnualPlusRiderChargeRate(policy.nric);
        decimal plusRiderSumInsured = policy.plusRiderSumInsured;

        //Calculate total annual insurance charge for basic plan and plus rider (E.g. 0.500 per $1000 of sum insured)
        decimal totalUserInsuranceChargePayable = basicSumInsured / 1000 * annualBasicInsuranceChargeRate + plusRiderSumInsured / 1000 * annualPlusRiderChargeRate;

        //Calculate insurance charge payable based on payment frequency (monthly, semi-annually, annually)
        if ("M".Equals(policy.premiumFrequency))
        {
            totalUserInsuranceChargePayable = totalUserInsuranceChargePayable / 12;
        }
        else if ("S".Equals(policy.premiumFrequency))
        {
            totalUserInsuranceChargePayable = totalUserInsuranceChargePayable / 2;
        }

        //Check existing funds invested by current policyholder
        List<Fund> funds = Fund.getActivePortfolioFunds(policyNo);

        //To specify different allocation of premiums paid (in form of $ amount)
        decimal investAmount = 0;
        decimal totalChargesAmount = 0;

        //For each fund invested by current policyholder
        foreach (Fund fund in funds)
        {   
            //Get its allocation percentage(from invest portion of premium paid) decided by current policyholder
            decimal fundPercentage = fund.allocation;
          
            //Get current fund prices
            FundPrice currentPrice = fund.findFundPrice(DateTime.Now);

            //Convert premiums paid for basic policy and plus rider into units according to each fund's allocation percentage
            decimal units = amount * fundPercentage / (currentPrice.offerPrice);

            decimal investUnits = 0;
            decimal otherChargesUnits = 0;
            decimal insuranceChargeUnits = 0;

            //Premium Allocation to Funds
            //Policy Year 1: 15%
            //Policy Year 2&3: 54%
            //Policy Year 4 to 6: 102% (for invest units after deduction of cost of insurance)
            //Policy Year 7 onwards: 105% (for invest units after deduction of cost of insurance)
            if (fundToPremiumPercentage > 1)
            {
                //Calculate no. of units to be deducted from each fund (according to allocation percentage) to pay for annual insurance charge
                insuranceChargeUnits = totalUserInsuranceChargePayable * fundPercentage / (currentPrice.offerPrice);

                //Calculate no. of units invested in each funds
                investUnits = (units - insuranceChargeUnits )* fundToPremiumPercentage;

                //No other charges from Policy Year 7 onwards
                otherChargesUnits = 0;

                totalChargesAmount += totalUserInsuranceChargePayable * fundPercentage;
                investAmount += amount * fundPercentage - totalChargesAmount; 
            }
            else
            {
                //Calculate no. of units invested in each funds
                investUnits = units * fundToPremiumPercentage;

                //Calculate no. of units required from each fund to pay for annual insurance charge and other charges 
                decimal unitsForInsuranceAndOtherCharges = units * (1 - fundToPremiumPercentage);

                //Calculate no. of units to be deducted from each fund (according to allocation percentage) to pay for annual insurance charge
                insuranceChargeUnits = totalUserInsuranceChargePayable * fundPercentage / (currentPrice.offerPrice);

                //Calculate no. of units to be deducted from each fund to pay for other administrative charges
                otherChargesUnits = unitsForInsuranceAndOtherCharges - insuranceChargeUnits;

                totalChargesAmount += amount * fundPercentage * (1 - fundToPremiumPercentage);
                investAmount += amount * fundPercentage * fundToPremiumPercentage;
            }

            

            //There will be no monthly insurance charge on the basic policy if the account value is greater than the basic policy sum insured
            if (accountValue <= basicSumInsured)
            {
                fund.insuranceChargeUnits += insuranceChargeUnits;
                fund.otherChargesUnits += otherChargesUnits;
            }
            else
            {
                investUnits += insuranceChargeUnits;
                fund.insuranceChargeUnits += 0;
                fund.otherChargesUnits += otherChargesUnits;
            }

            fund.buy(investUnits, DateTime.Now);

            //TODO:Jifen (parameters)
            fund.updateBalance(fund.investUnits, fund.insuranceChargeUnits,9m ,fund.otherChargesUnits);
        }

        
        //Allocate premium received from client's savings/current account for different purposes accordingly
        payment.payPremium(accountID, policyNo, investAmount, totalChargesAmount, waiverRiderPremium, amount, transactionId);   
    }



    public void terminate(string policyNo,int accountID)
    {
        //Check existing funds invested by current policyholder
        List<Fund> funds = Fund.getActivePortfolioFunds(policyNo);
        decimal accountValue = 0m;
        foreach(Fund fund in funds){
            //Get current fund price and calculate account value
            accountValue += fund.sell(fund.investUnits, DateTime.Now);
        }

        //Pay the amount of account value to the policyholder who wants to terminate his/her policy
        new Payment().payTerminate(accountID, policyNo,accountValue);
    }

    public decimal getAccountValue(string policyNo)
    {
        //Check existing funds invested by current policyholder
        List<Fund> funds = Fund.getActivePortfolioFunds(policyNo);
        decimal accountValue = 0m;
        foreach (Fund fund in funds)
        {
            //Get current fund price and calculate account value
            accountValue += fund.getAccountValue(DateTime.Now);
        }

        return accountValue;
    }
   
    public void switchFund(
                string policyNo, 
                Dictionary<string, string> sourcePercentageOrAmount,
                Dictionary<string, string> targetPercentage
        ){

        //Sell units from source funds according to $ amount or % decided by policyholder
        decimal amount = 0;
        foreach (String code in sourcePercentageOrAmount.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            string userInputValue = sourcePercentageOrAmount[code];
            if (userInputValue.StartsWith("$"))
            {
                decimal userInputAmount = Convert.ToDecimal(userInputValue.Substring(1));

                //Sell units from choosen fund according to the dollar amount to switch decided by policyholder
                decimal units = userInputAmount / fund.findFundPrice(DateTime.Now).bidPrice;
                amount += fund.sell(units, DateTime.Now);
            }
            else
            {
                //Sell units from choosen fund according to the percentage to switch decided by policyholder
                decimal userInputPercentage = Convert.ToDecimal(userInputValue.Replace("%", "")) / 100;
                amount += fund.sell(fund.investUnits * userInputPercentage, DateTime.Now);
            }

        }

        //Buy units for target funds according to the percentage allocation decided by policyholder
        foreach (String code in targetPercentage.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            //If target fund is not existing fund in the portfolio, add it as new fund to the portfolio
            if (Util.IsBlank(fund.code))
            {
                Fund.addNewFund(policyNo, code);
                fund.load(policyNo, code);
            }

            string userInputValue = targetPercentage[code];
            decimal userInputPercentage = Convert.ToDecimal(userInputValue) / 100;
            decimal units = amount * userInputPercentage / fund.findFundPrice(DateTime.Now).bidPrice;

            fund.buy(units,DateTime.Now);
        }
    }

    

    public void redirectFund(string policyNo, Dictionary<string, string> allocationPercentage)
    {
        List<Fund> activePortfolioFunds = Fund.getActivePortfolioFunds(policyNo);

        //For existing funds that are not included in the redirect fund list
        //it will be either update its allocation to 0%, or disabled
        foreach (Fund fund in activePortfolioFunds)
        {
            if (!allocationPercentage.Keys.Contains(fund.code))
            {
                if (fund.investUnits == 0)
                {
                    fund.disable();
                }
                else
                {
                    fund.updateAllocation(0);      
                }

            }

        }

        //For funds that are listed in the redirect fund list, 
        //if any fund's current status is INACTIVE, activate it and set the allocation percentage.
        //if any fund doesn't exist in the policy portfolio,add it as new fund into the portfolio
        foreach (String code in allocationPercentage.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            if (fund.code == null)
            {
                Fund.addNewFund(policyNo, code);
                fund.load(policyNo, code);
            }
            else
            {
                if(!fund.isActive){
                    fund.enable();
                }
            }
            string userInputValue = allocationPercentage[code];
            fund.updateAllocation(Convert.ToDecimal(userInputValue)/100);
        }
    }


 
    public void withdrawal(string policyNo,int accountID, String[] fundCodes, Dictionary<string,string> percentageOrAmount)
    {
        // check account value balance
        decimal totalBalance = 0;
        foreach (String code in fundCodes)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            string userInputValue = percentageOrAmount[code];
            if (userInputValue.StartsWith("$"))
            {
                decimal userInputAmount = Convert.ToDecimal(userInputValue.Substring(1));
                totalBalance += fund.getAccountValue(DateTime.Now) - userInputAmount;
            }
            else
            {
                totalBalance += fund.getAccountValue(DateTime.Now) * (1 - Convert.ToDecimal(userInputValue.Replace("%", "")) / 100);
            }
        }

        if (totalBalance < 1000)
        {
            throw new RetirementException(RetirementException.WITHDRAWAL_OVER_ACCOUNT_MIN_BALANCE);
        }

        //Actual withdrawal
        foreach (String code in fundCodes)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            string userInputValue = percentageOrAmount[code];
            if (userInputValue.StartsWith("$"))
            {
                decimal userInputAmount = Convert.ToDecimal(userInputValue.Substring(1));
                withdrawalBasedOnAmount(fund, accountID, userInputAmount);
            }
            else
            {
                decimal userInputPercentage = Convert.ToDecimal(userInputValue.Replace("%", "")) / 100;
                withdrawalBasedOnPercentage(fund, accountID, userInputPercentage);
            }
        }
    }


    private decimal getAnnualPlusRiderChargeRate(string nric)
    {
        decimal annualPlusRiderChargeRate = 0;

        Customer customer = Customer.getCustomerInfo(nric);

        DateTime zeroTime = new DateTime(1, 1, 1);
        TimeSpan span = DateTime.Now - customer.dob;

        // because we start at year 1 for the Gregorian 
        // calendar, we must subtract a year here.
        int age = (zeroTime + span).Year - 1;

        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT age, MN, MS, FN, FS" +
                      " FROM R_ANNUAL_PLUS_RIDER_CHARGE" +
                      " WHERE age =@age";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@age", (object)age ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            if (customer.gender.Equals("M"))
            {
                if (customer.smoker.Equals("Y"))
                {
                    annualPlusRiderChargeRate = dr.GetDecimal(2);
                }
                else
                {
                    annualPlusRiderChargeRate = dr.GetDecimal(1);
                }

            }
            else
            {
                if (customer.smoker.Equals("Y"))
                {
                    annualPlusRiderChargeRate = dr.GetDecimal(4);
                }
                else
                {
                    annualPlusRiderChargeRate = dr.GetDecimal(3);
                }
            }

        }

        dr.Close();
        conn.Close();

        return annualPlusRiderChargeRate;
    }


    private decimal getFundToPremiumPercentage(string policyNo)
    {
        Policy policy = Policy.getPolicy(policyNo);
        DateTime issueDate = policy.issueDate;

        DateTime zeroTime = new DateTime(1, 1, 1);
        TimeSpan span = DateTime.Now - issueDate;
        
        // because we start at year 1 for the Gregorian 
        // calendar, we must subtract a year here.
        int years = (zeroTime + span).Year - 1;

        //Premium Allocation to Funds
        //Policy Year 1: 15%
        //Policy Year 2&3: 54%
        //Policy Year 4 to 6: 102% (for invest units after deduction of cost of insurance)
        //Policy Year 7 onwards: 105% (for invest units after deduction of cost of insurance)
        if (years > 6)
        {
            return 1.05m;
        }

        if (years > 3)
        {
            return 1.02m;
        }

        if (years > 1)
        {
            return 0.54m;
        }

        return 0.15m;
    }

    private decimal getAnnualBasicInsuranceChargeRate(string nric)
    {
        decimal annualChargeRate = 0;

        Customer customer = Customer.getCustomerInfo(nric);

        DateTime zeroTime = new DateTime(1, 1, 1);
        TimeSpan span = DateTime.Now - customer.dob;

        // because we start at year 1 for the Gregorian 
        // calendar, we must subtract a year here.
        int age = (zeroTime + span).Year - 1;

        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT age, MN, MS, FN, FS" +
                      " FROM R_ANNUAL_INSURANCE_CHARGE" +
                      " WHERE age =@age";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@age", (object)age ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            if (customer.gender.Equals("M"))
            {
                if (customer.smoker.Equals("Y"))
                {
                    annualChargeRate = dr.GetDecimal(2);
                }
                else
                {
                    annualChargeRate = dr.GetDecimal(1);
                }

            }
            else
            {
                if (customer.smoker.Equals("Y"))
                {
                    annualChargeRate = dr.GetDecimal(4);
                }
                else
                {
                    annualChargeRate = dr.GetDecimal(3);
                }
            }
           
        }

        dr.Close();
        conn.Close();

        return annualChargeRate;
    }


    private void checkAmountPayable(Policy policy, decimal totalPremiumPaid)
    {
        decimal totalPremiumPayable = policy.totalAnnualPremium;

        //Calculate premium payable based on payment frequency (monthly, semi-annually, annually)
        if ("M".Equals(policy.premiumFrequency))
        {
            totalPremiumPayable = policy.totalAnnualPremium / 12;
        }
        else if ("S".Equals(policy.premiumFrequency))
        {
            totalPremiumPayable = policy.totalAnnualPremium / 2;
        }

        if (totalPremiumPaid < totalPremiumPayable) throw new RetirementException(RetirementException.PREMIUM_NOT_PAID_IN_FULL);
    }

    private void withdrawalBasedOnAmount(Fund fund, int accountID, decimal amount)
    {
        if (amount < 500) throw new RetirementException(RetirementException.WITHDRAWAL_LESS_THAN_MIN);
        fund.checkOverWithdrawal(amount);
        decimal units = amount / fund.findFundPrice(DateTime.Now).bidPrice;

        amount = fund.sell(units, DateTime.Now);
        new Payment().payWithdrawal(accountID, fund.policyNo, amount);
    }

    private void withdrawalBasedOnPercentage(Fund fund, int accountID, decimal percentage)
    {
        decimal amount = fund.getAccountValue(DateTime.Now) * percentage;
        if (amount < 500) throw new RetirementException(RetirementException.WITHDRAWAL_LESS_THAN_MIN);
        fund.checkOverWithdrawal(amount);
        decimal units = amount / fund.findFundPrice(DateTime.Now).bidPrice;

        amount = fund.sell(units, DateTime.Now);
        new Payment().payWithdrawal(accountID, fund.policyNo, amount);
    }


    public decimal getAnnualWaiverRiderPremiumRate(int age, string gender, string smokeStatus)
    {
        decimal annualWaiverRiderPremiumRate = 0;

        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT age, MN, MS, FN, FS" +
                      " FROM R_ANNUAL_INSURANCE_CHARGE" +
                      " WHERE age =@age";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@age", (object)age ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            if (gender.Equals("M"))
            {
                if (smokeStatus.Equals("Y"))
                {
                    annualWaiverRiderPremiumRate = dr.GetDecimal(2);
                }
                else
                {
                    annualWaiverRiderPremiumRate = dr.GetDecimal(1);
                }

            }
            else
            {
                if (smokeStatus.Equals("Y"))
                {
                    annualWaiverRiderPremiumRate = dr.GetDecimal(4);
                }
                else
                {
                    annualWaiverRiderPremiumRate = dr.GetDecimal(3);
                }
            }

        }

        dr.Close();
        conn.Close();

        return annualWaiverRiderPremiumRate;
    }

   /* public int getAccountId(string nric,string accountType)
    {
        int accountId = 0;
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT A.userId, accountId" +
                      " FROM ACCOUNT A, CUSTOMER B" +
                      " WHERE A.userId = B.userId AND B.nric =@nric AND A.accountType =@accountType AND A.currency =@currency";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@nric", (object)nric ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@accountType", (object)accountType ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@currency", "SGD"));

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            accountId = dr.GetInt32(1);
        }


        dr.Close();
        conn.Close();

        return accountId;
    }*/

    public int getUserId(string nric)
    {
        int id = 0;
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT userId" +
                      " FROM CUSTOMER " +
                      " WHERE nric =@nric";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@nric", (object)nric ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            id = dr.GetInt32(0);
        }


        dr.Close();
        conn.Close();

        return id;
    }


    public void topUp(string policyNo,decimal amount,
                Dictionary<string, string> targetPercentage
        )
    {
        //Buy units for target funds according to the percentage allocation decided by policyholder
        foreach (String code in targetPercentage.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            //If target fund is not existing fund in the portfolio, add it as new fund to the portfolio
            //if (Util.IsBlank(fund.code))
            //{
            //    Fund.addNewFund(policyNo, code);
            //    fund.load(policyNo, code);
            //}

            string userInputValue = targetPercentage[code];
            decimal userInputPercentage = Convert.ToDecimal(userInputValue) / 100;
            decimal units = amount * userInputPercentage / fund.findFundPrice(DateTime.Now).bidPrice;

            fund.buy(units, DateTime.Now);
        }
    }

    

}